#国控站点排名情况
library(tidyverse)
library(lubridate)

conn <- src_sqlite("~/Documents/rockontrol/合肥项目数据/合肥市项目.db")

begin <- "2019-01-01"
end <- "2019-06-30"

town <-  "包河区"

dataset <- tbl(conn,"日数据") %>%
  inner_join(tbl(conn,"站点信息"),by  =c("站点名称"="站点")) %>%
  filter(类型 == "国控站",日期>= begin,日期<= end )  %>% collect()



dataset <- dataset %>%mutate(flag = ifelse(区县== town ,T,F )) %>% group_by(站点名称,flag)%>%
  summarize(SO2浓度 =round( mean(SO2浓度,na.rm = TRUE)),
            PM2.5浓度 =round( mean(PM2.5浓度,na.rm = TRUE)),
            PM10浓度 = round(mean(PM10浓度,na.rm = TRUE)),
            NO2浓度 = round(mean(NO2浓度,na.rm = TRUE)),
            CO浓度 = round(quantile(CO浓度,0.95),1),
            O3浓度= round(quantile(O3浓度,0.9)),
            综合指数 = round( SO2浓度*100/60)/100+
            round( PM2.5浓度*100/35)/100+
            round(PM10浓度*100/70)/100+
             round(NO2浓度*100/40)/100+
             round(CO浓度*100/4)/100+
            round(O3浓度*100/160)/100
            )%>%
  select (站点名称,flag,"SO[2]~ug/m^3" = SO2浓度,"NO[2]~ug/m^3" = NO2浓度,"CO~mg/m^3" = CO浓度,"O[3]~ug/m^3" = O3浓度,"PM[2.5]~ug/m^3" = PM2.5浓度,"PM[10]~ug/m^3" = PM10浓度) %>% 
  gather(key = 项,value = 浓度,... = 3:8) 
  




fun <- function(x) { return(sub("\ .*","",x))}

 

dataset  %>% ggplot(aes(x =reorder(paste(站点名称,项),浓度),y = 浓度)) +
  geom_bar(stat = "identity",aes(fill = flag),width =0.5) + 
  scale_fill_manual(values =  c("#6495ED","#FF4500"))+
  labs(x= "",y = "浓度")+ 
  scale_x_discrete(labels = fun)+ 
  facet_wrap(项 ~.,scales  = "free",labeller = label_parsed)+theme_bw() +
  theme(  plot.title =   element_text(hjust = 0.5),
          axis.text.x=element_text(angle = -90,size = 15,vjust = 0.5),
          axis.title.y = element_text(size = 15),
          axis.text.y = element_text(size = 15),
          strip.text = element_text(size= 15),legend.text.align = 0,legend.title = element_blank(),
          legend.text = element_text(size = 15),legend.position = "none"
  ) 

DBI::dbDisconnect(conn$con)


